<?php
session_start();
include_once("config/config.inc.php");

$lsql = "select settings_value from settings where id = '3'";
$lres = mysql_query($lsql);
list($lobbyid)=mysql_fetch_row($lres);


function getStartTime()
{
	$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc";
		
	$res = mysql_query($sql);
	while(list($time) = mysql_fetch_row($res))
	{
		return $time;
	}	
}


function getCheckoutReport($start,$end,$suser_id,$euser_id,$lobbyid)
{
	$sql = "select settings_value from settings where id = '1'";
	$res = mysql_query($sql);
	list($value) = mysql_fetch_row($res);
	$ret.="<div class='report'>";
	$ret.= "<b>".strtoupper($value)."</b><br>";
	$ret.= "<b>ROOM CHECKOUT REPORT</b><br>";
	
	//$ret.= "<b>PRINTED TIME: </b>".date("l m/d/Y g:i:s A")."<br>";	
	$ret.= "<b>SHIFT: </b>".getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y g:i:s A",strtotime($start));
	$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
	if($suser_id == $euser_id)
	{
		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($cashier)=mysql_fetch_row($_res);
		$ret.=$cashier;
	}else
	{
		//$_sql = "select fullname from users where user_id = '$suser_id'";
		//$_res = mysql_query($_sql);
		//list($scashier)=mysql_fetch_row($_res);
		//$ret.=$scashier;

		//$ret.=" - ";

		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($ecashier)=mysql_fetch_row($_res);
		$ret.=$ecashier;
	}
	$ret.= "<br>";
	$ret.="</div>";
	$ret.= "<br>";
	$ret.= "<br>";
	$sql = "select * from occupancy a, rooms b, room_types c 
	where a.actual_checkin <= '$start' 
	and a.actual_checkout >= '$end' ) 
	and a.room_id = b.room_id
	and c.room_type_id = b.room_type_id
	order by c.rank asc,
	a.rate_id desc,
	a.actual_checkout asc";
	

	$res = mysql_query($sql);

	$ret.= "<table class='report' cellpadding=\"5\"  >";
	$ret.= "<tr>";
	//$ret.= "<th>OCCUPANCY</th>";
	$ret.= "<th>RM_TYPE</th>";
	$ret.= "<th>RM_NO</th>";
	$ret.= "<th>HRS</th>";
	$ret.= "<th>RATE</th>";
	$ret.= "<th>OT</th>";	
	$ret.= "<th>DISC</th>";
	$ret.= "<th>INTOT</th>";
	$ret.= "<th>CHECKIN </th>";
	$ret.= "<th>CHECKOUT </th>";
	$ret.= "<th>FOOD</th>";
	$ret.= "<th>BEER</th>";
	$ret.= "<th>MISC</th>";
	$ret.= "<th>ADJUST</th>";
	$ret.= "<th>DEDUCT</th>";
	$ret.= "<th>OT</th>";
	$ret.= "<th>TOTAL</th>";
	$ret.= "</tr>";
	/***********
		LOBBY
	************/
	$ret.= "<tr>";
	$ret.= "<td>Lobby</td>";
	$ret.= "<td>Lobby</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";	
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";

	//Lobby Food
	$_sql = "select sum(unit_cost*qty) from fnb_sales 
			where status in ('Paid')
			and category_id <> '21'
			and category_id <> '17'
			and occupancy_id = '$lobbyid'
			and update_date >= '$start' 
			and update_date <= '$end' ";
	$_res = mysql_query($_sql) or die(mysql_error());
	$fnbtot =0;
	list($fnbtot)=mysql_fetch_row($_res);
	
	/*
	$fnbtot =0;
	while(list($fnblobbyamount)=mysql_fetch_row($_res))
	{
		$fnblobbytot = $fnblobbytot + $fnblobbyamount;
	}
	*/

	//Lobby Beer 
	$_sql = "select unit_cost*qty from fnb_sales 
		where status in ('Paid')
		and (category_id = '21'
		or category_id = '17')
		and occupancy_id = '$lobbyid'
		and update_date >= '$start' 
		and update_date <= '$end' ";
	$_res = mysql_query($_sql);
	$beertot = 0;
	while(list($beerlobbyamount)=mysql_fetch_row($_res))
	{
		$beerlobbytot = $beerlobbytot + $beerlobbyamount;
	}

	//misc
	$_sql = "select sum(unit_cost*qty) from room_sales 
		where status in ('Paid')
		and category_id <> '3'
		and occupancy_id = '$lobbyid'
		and update_date >= '$start' 
		and update_date <= '$end' ";
			
	$_res = mysql_query($_sql);
	$misctot = 0;
	list($misctot)=mysql_fetch_row($_res);
	/*
	$misctot = 0;
	while(list($misclobbyamount)=mysql_fetch_row($_res))
	{
		$misclobbytot = $misclobbytot + $misclobbyamount;
	}

	$lobbytotal = $fnblobbytot + $beerlobbytot + $misclobbytot;
	*/

	if($fnblobbytot=='')$fnblobbytot='';
	else $fnblobbytot =number_format($fnblobbytot,2);
	if($beerlobbytot=='')$beerlobbytot='';
	else $beerlobbytot=number_format($beerlobbytot,2);
	if($misclobbytot=='')$misclobbytot='';
	else $misclobbytot=number_format($misclobbytot,2);
	if($lobbytotal=='0')$lobbytotal='0.00';
	else $lobbytotal=number_format($lobbytotal);
	

	$ret.= "<td>$fnblobbytot</td>";
	$ret.= "<td>$beerlobbytot</td>";
	$ret.= "<td>$misclobbytot</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>$lobbytotal</td>";
	$ret.= "</tr>";

	while($row = mysql_fetch_array($res))
	{
		$_sql = "select a.room_type_id,a.door_name,b.room_type_name 
				from rooms a, room_types b 
				where a.room_type_id=b.room_type_id 
				and a.room_id = '".$row["room_id"]."'";
		$_res = mysql_query($_sql);
		list($id,$door_name,$room_type_name)=mysql_fetch_row($_res);
		/* <jammer 3/1/2010>
		$_sql = "select room_type_name from room_types where room_type_id = '$id'";
		$_res = mysql_query($_sql);
		list($room_type_name)=mysql_fetch_row($_res);
		*/


		$ret .="<tr>";
		//$ret .="<td>".$row["occupancy_id"]."</td>";
		$ret .="<td>".$room_type_name."</td>";
		$ret .="<td>".$door_name."</td>";

		$_sql = "select rate_name from rates where rate_id = '".$row["rate_id"]."'";
		$_res = mysql_query($_sql);
		list($rate_name)=mysql_fetch_row($_res);
		$rate_name = str_replace("HRS","",$rate_name);
		$ret .="<td>".$rate_name."</td>";

		$_sql = "select unit_cost*qty from room_sales where occupancy_id = '".$row["occupancy_id"]."' 
				and status in ('Paid')
				and item_id = '15'
				order by roomsales_id desc
				limit 0,1";
		$_res = mysql_query($_sql);
		list($cost)=mysql_fetch_row($_res);
		
		$ret .="<td>".number_format($cost)."</td>";

		$_sql = "select a.unit_cost*a.qty, a.roomsales_id
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			AND b.transaction_date = a.update_date
			AND a.item_id = '16'
			AND b.transaction_type in ('CheckIn')
			AND a.status in ('Paid')
			AND a.occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		list($in_ot,$inextid)=mysql_fetch_row($_res);
		if($in_ot=='')
		{
			$din_ot = '';	
		}else{
			$din_ot=$in_ot;
		}	
		$ret .="<td>".$din_ot."</td>";

		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '17'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		list($disc)=mysql_fetch_row($_res);
		if(!$disc) {
			$disc ='';
		}else{
			$disc=abs($disc);
		}
		$ret .="<td>".$disc."</td>";
		

		$in_total = ($cost+$in_ot)-abs($disc);

		$ret .="<td>".$in_total."</td>";

		

		$ret .="<td NOWRAP>".date("m/d/y - g:i A", strtotime($row["actual_checkin"]))."</td>";

		$ret .="<td NOWRAP>".date("m/d/y - g:i A", strtotime($row["actual_checkout"]))."</td>";

		
		$_sql = "select unit_cost*qty
			FROM room_sales
			WHERE item_id = '16'
			AND roomsales_id not in ('$inextid') 
			AND occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		$out_ot1 = 0;
		while(list($ecost)=mysql_fetch_row($_res))
		{
			$out_ot1 += $ecost;
		}
		
		

		

		//food
		$_sql = "select sum(unit_cost*qty) from fnb_sales 
			where status in ('Paid')
			and category_id <> '21'
			and category_id <> '17'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql) or die(mysql_error());
		$fnbtot =0;
		list($fnbtot)=mysql_fetch_row($_res);
		/*
		while(list($fnbamount)=mysql_fetch_row($_res))
		{
			$fnbtot = $fnbtot + $fnbamount;
		}
		*/
		
		//beer 
		$_sql = "select sum(unit_cost*qty) from fnb_sales 
			where status in ('Paid')
			and (category_id = '21'
			or category_id = '17')
			and sales_date between '$start' and '$end'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		$beertot = 0;
		list($beertot)=mysql_fetch_row($_res);
		/*
		while(list($beeramount)=mysql_fetch_row($_res))
		{
			$beertot = $beertot + $beeramount;
		}
		*/

		//misc
		$_sql = "select sum(unit_cost*qty) from room_sales 
			where status in ('Paid')
			and category_id <> '3'
			and sales_date between '$start' and '$end'
			and occupancy_id = '".$row["occupancy_id"]."'";
			
		$_res = mysql_query($_sql);
		$misctot = 0;
		list($misctot )=mysql_fetch_row($_res);
		/*
		while(list($miscamount)=mysql_fetch_row($_res))
		{
			$misctot = $misctot + $miscamount;
		}
		*/
		
		
		

		//adjustment
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '18'
			and sales_date between '$start' and '$end'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		$adjtot = 0;
		while(list($adjamount)=mysql_fetch_row($_res))
		{
			$adjtot = $adjtot + $adjamount;
		}

		//deduction
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '27'
			and sales_date between '$start' and '$end'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_res = mysql_query($_sql);
		$detot = 0;
		while(list($deamount)=mysql_fetch_row($_res))
		{
			$detot = $detot + $deamount;
		}

		
		

		if($fnbtot=='0.00')$fnbtot ='';
		else $fnbtot =number_format($fnbtot,2);
		if($beertot=='0.00')$beertot ='';
		else $beertot =number_format($beertot,2);
		if($misctot=='0.00')$misctot ='';
		else $misctot =number_format($misctot,2);
		if($adjtot=='0.00')$adjtot ='';
		else $adjtot =number_format($adjtot,2);
		$ret .="<td>".$fnbtot."</td>";	
		$ret .="<td>".$beertot."</td>";	
		$ret .="<td>".$misctot."</td>";
		$ret .="<td>".$adjtot."</td>";	
		$ret .="<td>(".number_format(abs($detot),2).")</td>";	
		
		
		$ot_total = $in_ot+$out_ot1;
		if($ot_total=='0.00')$ot_total ='';
		
		$total = ($in_total + $out_ot1 + $fnbtot + $misctot + $beertot + $adjtot) - abs($detot);
		$ret .="<td>".$ot_total."</td>"; //OT TOTAL
		$ret .="<td>".number_format($total)."</td>";	
		$ret .="</tr>";	
		$ftotal += $total;
		
		}
		$ret .="</table>";
		//$ret .= getCheckoutSummary($start,$end,$suser_id,$euser_id,$lobbyid);
		//echo $ftotal;
		return $ret;
		
}



function getCheckoutSummary($start,$end,$suser_id,$euser_id,$lobbyid)
{
	$sql = "SELECT `room_type_id`, `room_type_name`, `site_id` FROM `room_types` order by rank asc";
	
	$ret .= "<div class='report'>";
	$ret .= "<b>Rechit SUMMARY (Inclusive of food, misc, beer and others)</b><br>";	
	$ret.= "<b>SHIFT: </b>".getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y - g:i:s A",strtotime($start));
	$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
	if($suser_id == $euser_id)
	{
		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($cashier)=mysql_fetch_row($_res);
		$ret.=$cashier;
	}else
	{
		$_sql = "select fullname from users where user_id = '$suser_id'";
		$_res = mysql_query($_sql);
		list($scashier)=mysql_fetch_row($_res);
		$ret.=$scashier;

		$ret.=" - ";

		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($ecashier)=mysql_fetch_row($_res);
		$ret.=$ecashier;
	}
	$ret.= "<br>";
	$ret.="</div>";	
	$ret .= "<table cellpadding=5 cellspacing=5 class='summary'>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>#TOTAL</td>";
	$ret .= "<td>3 HRS</td>";
	$ret .= "<td>12 HRS</td>";
	$ret .= "<td>24 HRS</td>";
	$ret .= "<td>ROOM</td>";
	$ret .= "<td>OT</td>";
	$ret .= "<td>FOOD</td>";
	$ret .= "<td>BEER</td>";
	$ret .= "<td>MISC</td>";
	$ret .= "<td>ADJUST</td>";
	$ret .= "<td>DEDUC</td>";
	$ret .= "<td>TOTAL</td>";	
	$ret .= "</tr>";

	$ret .= "<tr>";
	$ret .= "<td>Lobby</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";


	$_sql = "select unit_cost*qty from fnb_sales 
			where status in ('Paid')
			and category_id <> '21'
			and category_id <> '17'
			and occupancy_id = '$lobbyid'
			and update_date >= '$start' 
			and update_date <= '$end' ";
	$_res = mysql_query($_sql) or die(mysql_error());
	$fnbtot =0;
	while(list($fnblobbyamount)=mysql_fetch_row($_res))
	{
		$fnblobbytot = $fnblobbytot + $fnblobbyamount;
	}

	//Lobby Beer 
	$_sql = "select unit_cost*qty from fnb_sales 
		where status in ('Paid')
		and (category_id = '21'
		or category_id = '17')
		and occupancy_id = '$lobbyid'
		and update_date >= '$start' 
		and update_date <= '$end' ";
	$_res = mysql_query($_sql);
	$beertot = 0;
	while(list($beerlobbyamount)=mysql_fetch_row($_res))
	{
		$beerlobbytot = $beerlobbytot + $beerlobbyamount;
	}

	//misc
	$_sql = "select unit_cost*qty from room_sales 
		where status in ('Paid')
		and category_id <> '3'
		and occupancy_id = '$lobbyid'
		and update_date >= '$start' 
		and update_date <= '$end' ";			
	$_res = mysql_query($_sql);
	$misctot = 0;
	while(list($misclobbyamount)=mysql_fetch_row($_res))
	{
		$misclobbytot = $misclobbytot + $misclobbyamount;
	}

	$lobbytotal = $fnblobbytot + $beerlobbytot + $misclobbytot;

	$ret .= "<td>$fnblobbytot</td>";
	$ret .= "<td>$beerlobbytot</td>";
	$ret .= "<td>$misclobbytot</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td><b>$lobbytotal</b></td>";	
	$ret .= "</tr>";

	$ret .= "<tr>";
	$ret .= "<td colspan=13>			
			<div style='border-width:3px;
			border-top-color:black;
			border-top-style:solid;
			border-right-style:hidden;
			border-bottom-style:hidden;
			border-left-style:hidden;
			text-align:left'>
			
			</div>			
			</td>";			
	$ret .= "</tr>";
	
	$site = "2";
	$ctr = 1;
	$res = mysql_query($sql)or die($sql.mysql_error());
	while(list($room_type_id,$room_type_name,$site_id) = mysql_fetch_row($res))
	{
		
		

		$ret .= "<tr>";
		$ret .= "<td>".$room_type_name."</td>
		<td>".getNumRoomByTypeID($room_type_id,$start,$end)."</td>";
		
		
		//$_sql = "select b.rate_name, count(a.occupancy_id) from (rates b left join occupancy a  on a.rate_id=b.rate_id ) left join rooms c
		//		on a.room_id=c.room_id 
		//		where a.actual_checkout >= '$start' 
		//		and a.actual_checkout <= '$end'
		//		and c.room_type_id = '$room_type_id'
		//		and count(a.occupancy_id) = ''
		//		group by a.rate_id 
		//		limit 0,3";

		$_sql = "select rate_id from rates limit 0,3";
		$_res = mysql_query($_sql);
		while(list($rate_id)=mysql_fetch_row($_res))
		{
			$__sql = "select count(a.occupancy_id) from rates b , occupancy a, rooms c  
			where a.rate_id=b.rate_id
			and c.room_id = a.room_id
			and a.actual_checkout = '0000-00-00 00:00:00'
			and b.rate_id = '$rate_id'
			and c.room_type_id = '$room_type_id'";
			$__res = mysql_query($__sql);
			list($cnt)=mysql_fetch_row($__res);
			if($cnt=='0')$cnt='&nbsp;';		
			$ret .= "<td>$cnt</td>";
			
		}

		//food
		$_sql = "select sum(b.unit_cost*b.qty) from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			and a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and (b.category_id <> '21' and b.category_id <> '17')";
		$_res = mysql_query($_sql) or die(mysql_error());
		$fnbtot ="";
		list($fnbtot)=mysql_fetch_row($_res);
		
		/*
		while(list($fnbamount)=mysql_fetch_row($_res))
		{
			$fnbtot = $fnbtot + $fnbamount;
		}
		*/

		//beer
		$_sql = "select b.unit_cost*b.qty from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			or a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and (b.category_id = '21' or b.category_id = '17')";


		$_res = mysql_query($_sql) or die(mysql_error());
		$beertot ="";
		while(list($beeramount)=mysql_fetch_row($_res))
		{
			$beertot = $beertot + $beeramount;
		}

		//misc
		$_sql = "select b.unit_cost*b.qty from occupancy a, room_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			or a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and b.category_id <> '3'";
		

		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$_num = mysql_num_rows($_res);
		$misctotal = "";
		while(list($misccost)=mysql_fetch_row($_res))
		{			
			$misctotal = $misctotal + $misccost;				
		}

		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			or a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '17'";
	

		$_res = mysql_query($_sql);
		while(list($disc)=mysql_fetch_row($_res))
		{
			$disctotal += $disc;
		}
		
		
		//adjustment
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			and a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '18'";
		

		$_res = mysql_query($_sql);

		$adjtot = 0;
		while(list($adjamount)=mysql_fetch_row($_res))
		{
			$adjtot = $adjtot + $adjamount;
		}

		//deduction		
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout = '0000-00-00 00:00:00'
			and a.actual_checkin < b.sales_date
			and b.sales_date between '$start' and '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '27'";

		$_res = mysql_query($_sql);

		$detot = 0;
		while(list($deamount)=mysql_fetch_row($_res))
		{
			$detot = $detot + $deamount;
		}

		$detot = abs($detot);

		$roomtot = getRoomTotalByTypeID($room_type_id,$start,$end) + $disctotal;

		$disctotal = 0;
		$ottot = getOTByRoomTypeID($room_type_id,$start,$end);
		
		if($roomtot>0)$froomtot=number_format($roomtot);else $froomtot='';
		if($ottot>0)$fottot=number_format($ottot);else $fottot='';
		if($fnbtot>0)$ffnbtot=number_format($fnbtot);else $ffnbtot='';
		if($beertot>0)$fbeertot=number_format($beertot);else $fbeertot='';
		if($misctotal>0)$fmisctotal=number_format($misctotal);else $fmisctotal='';
		if($adjtot>0)$fadjtot=number_format($adjtot);else $fadjtot='';
		if($detot>0)$fdetot=number_format($detot);else $fdetot='';
		
		$ret .= "<td>".$froomtot."</td>";
		$ret.= "<td>".$fottot."</td>";
		$ret .= "<td>".$ffnbtot."</td>";
		$ret .= "<td>".$fbeertot."</td>";
		$ret .= "<td>".$fmisctotal."</td>";		
		$ret .= "<td>".$fadjtot."</td>";
		$ret .= "<td>".$fdetot."</td>";	
		$sitetotal += ($roomtot + $ottot + $fnbtot + $beertot + $misctotal + $adjtot) - ($detot);
		$siteroomtotal += $roomtot;
		$siteottotal += $ottot;
		$sitefnbtotal += $fnbtot;
		$sitebeertotal += $beertot;
		$sitemisctotal += $misctotal;
		$siteadjtot += $adjtot;
		$sitedetot += $detot;
		$grandtotal = ($roomtot + $ottot + $fnbtot + $beertot + $misctotal + $adjtot) - ($detot);

		$ret.= "<td>".number_format($grandtotal)."</td>";		
		$ret .= "</tr>";

		 
		$i++;

		if($ctr == 6)
		{			
			$ret .= "<tr>";
			$ret .= "<td colspan=13>			
			<div style='border-width:3px;
			border-top-color:black;
			border-top-style:solid;
			border-right-style:hidden;
			border-bottom-style:hidden;
			border-left-style:hidden;
			text-align:left'>
			
			</div>			
			</td>";			
			$ret .= "</tr>";
			$ret .= "<tr>";
			$sql1 = "select site_name from sites where site_id = '$site'";
			$res1 = mysql_query($sql1);
			list($sitename)=mysql_fetch_row($res1);
			$ret .= "<td colspan='5'><b>$sitename TOTAL</b></td>";			
			$ret .= "<td>".number_format($siteroomtotal)."</td>";
			$ret .= "<td>".number_format($siteottotal)."</td>";
			$ret .= "<td>".number_format($sitefnbtotal)."</td>";
			$ret .= "<td>".number_format($sitebeertotal)."</td>";
			$ret .= "<td>".number_format($sitemisctotal)."</td>";			
			$ret .= "<td>".number_format($siteadjtot)."</td>";
			$ret .= "<td>".number_format($sitedetot)."</td>";
			$ret .= "<td><b>".number_format($sitetotal)."</b></td>";
			$ret .= "</tr>";
			
			$gtotal += $sitetotal;
			$sitetotal = 0;
			$siteroomtotal = 0;
			$siteottotal = 0;
			$sitefnbtotal = 0;
			$sitebeertotal = 0;
			$sitemisctotal = 0;
			$siteadjtot = 0;
			$sitedetot = 0;

			$site += 1;
			
		}
		
		$ctr = $ctr + 1;
		
		
	}
	$ret .= "<tr>";
		$ret .= "<td colspan=13>			
			<div style='text-align:right'>
			<br><br><br>
			<b>GRAND TOTAL: ".number_format($gtotal+$lobbytotal)."<b>
			</div>
			</td>";	
		$ret .= "</tr>";
	$ret .= "</table>";
	



	return $ret;
}

function getRoomTotalByTypeID($room_type_id,$startshift,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout = '0000-00-00 00:00:00'
	or a.actual_checkout > '$end'
	and b.room_type_id = '$room_type_id'";
	$res = mysql_query($sql);
	$total = 0;
	while(list($id)=mysql_fetch_row($res))
	{
		$_sql = "select unit_cost*qty from room_sales where item_id = '15' and occupancy_id = '$id' and sales_date between '$startshift' and '$end' ";
		$_res = mysql_query($_sql);
		list($unitqty)=mysql_fetch_row($_res);
		$total += $unitqty;
	}

	return $total;
	
}

function getNumRoomByTypeID($room_type_id,$startshift,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout = '0000-00-00 00:00:00'
	or a.actual_checkout > '$end'
	and b.room_type_id = '$room_type_id'";
	$res = mysql_query($sql);
	$num = mysql_num_rows($res);
	return $num;
}
function getAmountByRoomTypeID($roomtypeid,$startdt,$end)
{
	
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout = '0000-00-00 00:00:00'
	
	and b.room_type_id = '$roomtypeid'";
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{		
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where item_id ='15' 
		and occupancy_id in ($id)
		and sales_date between '$startshift' and '$end'
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$total = $total + ($unit_cost * $qty);
			
		}
		//food and beer
		$_sql = "select unit_cost*qty from fnb_sales 
			where status in ('Paid')
			and sales_date between '$startshift' and '$end'
			and occupancy_id in ($id)";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($cost)=mysql_fetch_row($_res))
		{
			$total = $total + $cost;			
		}

		//adjustment
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '18'
			and sales_date between '$startshift' and '$end'
			and occupancy_id in ($id)";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);		
		while(list($cost)=mysql_fetch_row($_res))
		{			
			$total = $total + $cost;				
		}

		//misc
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and category_id <> 3
			and sales_date between '$startshift' and '$end'
			and occupancy_id in ($id)";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($cost)=mysql_fetch_row($_res))
		{			
			$total = $total + $cost;				
		}

		//deduction
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '27'
			and sales_date between '$startshift' and '$end'
			and occupancy_id in ($id)";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$dtotal = 0;
		while(list($cost)=mysql_fetch_row($_res))
		{			
			$dtotal = $dtotal + $cost;			
		}
		
		$total = $total - abs($dtotal);
	}
	
	return $total;
}
function getOTByRoomTypeID($roomtypeid,$startdt,$end)
{
	
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout = '0000-00-00 00:00:00'
	and b.room_type_id = '$roomtypeid'";
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{		
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where item_id ='16' 
		and occupancy_id in ($id) 
		and sales_date between '$startshift' and '$end'
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
	return $total;
}

function getLatestShifts($shiftid)
{
	$sql = "SELECT `shift-transaction_id`,datetime,user_id  FROM `shift-transactions` where shift = 'start' order by datetime desc ";
	$res = mysql_query($sql);
	$ret = "<select name='rblshifts' id='rblshifts' onchange='myform.submit();'>";
	$ret .= "<option value=''>&nbsp;</option>";
	while(list($shift_transaction_id,$datetime,$userid)=mysql_fetch_row($res))
	{
		if($shiftid == $shift_transaction_id)
		{
			$select = "selected";
		}else
		{
			$select = " ";
		}
		$__sql = "SELECT user_id  FROM `shift-transactions` where shift = 'end' 
		and `datetime` > '$datetime'
		order by datetime asc 
		limit 0,1";
		$__res = mysql_query($__sql);
		list($userid)=mysql_fetch_row($__res);
		$_sql = "select fullname from users where user_id = '$userid'";
		$_res = mysql_query($_sql);
		list($username)=mysql_fetch_row($_res);
		$ret .= "<option value='$shift_transaction_id' $select>$datetime - $username - ".getshift($datetime)."</option>";
	}
	$ret .= "</select>";

	return $ret;
}


$shiftid = $_POST["rblshifts"];
$sql = "select datetime,user_id from `shift-transactions` where `shift-transaction_id` = '$shiftid'";
$res = mysql_query($sql);
list($sdatetime,$suser_id)=mysql_fetch_row($res);
$startshift = $sdatetime;


$sql = "select datetime,user_id from `shift-transactions` where datetime  > '$startshift' order by datetime asc limit 0,1";
$res = mysql_query($sql);
list($edatetime,$euser_id)=mysql_fetch_row($res);
$end = $edatetime;

if(!$end)
{
	$end = date("Y-m-d H:i:s");
}


function getshift($date) {
	if(!$date)$date=date("Y-m-d H:i:s");
	list($d, $t) = explode(" ", $date);
	list($h, $m, $s) = explode(":", $t);
	$sql = "select shift_id from shifts where $h between shift_start and shift_end";
	$res = mysql_query($sql) or die(mysql_error() .$sql);
	list($shift)=mysql_fetch_row($res);
	
	if($h==16||$h==15)
	{
		return $shift = "3rd";
	}
	elseif($h==8||$h==7)
	{
		return $shift = "2nd";
	}
	return  "1st";
}
?>
<style>
		.printable {
			font-family: sans-serif;
			font-size: 14px;
			font-weight: 550;
			}
		.report{
			font-family: sans-serif;
			font-size: 14px ;
			text-align:left;
			font-weight: 550;
				
		}
		.report td{
			padding-bottom:12px;
		}
		.summary{
			font-family: sans-serif;
			font-size: 14px;
			text-align:left;
			font-weight: 550;
		}
</style>
<script src="../js/jquery.js" type="text/javascript"></script>
<script type="text/javascript" src="../js/jquery.print.js"></script>
<script type="text/javascript">
 
		// When the document is ready, initialize the link so
		// that when it is clicked, the printable area of the
		// page will print.
		$(document).ready(function(){		
				$("a").attr( "href", "javascript:void( 0 )" ).click(
						function(){
							// Print the DIV.
							$(".printable2").print();
							$(".printable").print(); 							
							// Cancel click event.
							return( false );
						});
 
			
		});
 
</script>
<form name=myform method=post>
<div>
<? echo getLatestShifts($shiftid); ?>
</div>
<br>
<a href="#">Print Report</a>
<br>
<br>
<div class='printable'>
<? //echo getCheckoutReport($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
</div>
<br>
<br>
<br>
<div class='printable2'>
<? echo getCheckoutSummary($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
</div>
<br />
<a href="#">Print Report</a>
</form>